package com.isoft.util;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class SqlParserUtil {

    public static List<SqlTable> parseSql(String sqlContent) {
        List<SqlTable> tables = new ArrayList<>();
        Map<String, SqlTable> tableMap = new HashMap<>();
        Map<String, String> comments = new HashMap<>();

        // 简化SQL处理
        sqlContent = sqlContent.replaceAll("(?i)IF NOT EXISTS", "")
                .replaceAll("(?i)COLLATE [^,)]+", "")
                .replaceAll("(?i)DEFAULT [^,)]+", "")
                .replaceAll("(?i)NOT NULL", "")
                .replaceAll("\\s+", " ");

        // 第一次遍历：提取表结构和注释
        String[] statements = sqlContent.split(";");
        for (String stmt : statements) {
            stmt = stmt.trim();
            if (stmt.isEmpty()) continue;

            // 解析CREATE TABLE
            if (stmt.toUpperCase().startsWith("CREATE TABLE")) {
                SqlTable table = parseCreateTable(stmt);
                if (table != null) {
                    tableMap.put(table.getTableName().toLowerCase(), table);
                }
            }
            // 解析字段注释
            else if (stmt.toUpperCase().startsWith("COMMENT ON COLUMN")) {
                parseColumnComment(stmt, comments);
            }
            // 解析表注释
            else if (stmt.toUpperCase().startsWith("COMMENT ON TABLE")) {
                parseTableComment(stmt, comments);
            }
        }

        // 第二次遍历：关联注释到字段
        for (SqlTable table : tableMap.values()) {
            String tableKey = table.getTableName().toLowerCase();

            // 关联字段注释
            for (SqlColumn column : table.getColumns()) {
                String columnKey = tableKey + "." + column.getName().toLowerCase();
                column.setComment(comments.getOrDefault(columnKey, ""));
            }

            // 关联表注释
            table.setDescription(comments.getOrDefault("table." + tableKey, ""));

            tables.add(table);
        }

        return tables;
    }

    private static SqlTable parseCreateTable(String sql) {
        SqlTable table = new SqlTable();

        // 提取表名 (处理带schema和不带schema的情况)
        Pattern tablePattern = Pattern.compile("(?i)CREATE TABLE\\s+(?:\"[^\"]+\"\\.)?\"([^\"]+)\"");
        Matcher tableMatcher = tablePattern.matcher(sql);
        if (tableMatcher.find()) {
            table.setTableName(tableMatcher.group(1));
        } else {
            log.warn("无法解析表名: {}", sql);
            return null;
        }

        // 提取字段定义 - 更健壮的正则表达式
        Pattern columnPattern = Pattern.compile(
                "\"([^\"]+)\"\\s+([^\\s,()]+)(?:\\([^)]*\\))?\\s*(?:COLLATE\\s+[^\\s,]+)?\\s*(?:DEFAULT\\s+[^\\s,]+)?\\s*(?:NOT NULL)?"
        );
        Matcher columnMatcher = columnPattern.matcher(sql);

        List<SqlColumn> columns = new ArrayList<>();
        while (columnMatcher.find()) {
            SqlColumn column = new SqlColumn();
            column.setName(columnMatcher.group(1));

            // 获取字段类型
            String type = columnMatcher.group(2);
            if (type == null || type.trim().isEmpty()) {
                // 如果无法解析类型，使用默认值
                type = "UNKNOWN";
                log.warn("字段 {} 类型解析失败: {}", column.getName(), sql);
            } else {
                // 简化类型处理
                type = type.toUpperCase();
                if (type.contains("(")) {
                    type = type.substring(0, type.indexOf('('));
                }
                // 应用类型映射
                type = getFieldType(type);
            }
            column.setType(type);

            columns.add(column);
        }

        // 如果未解析到任何字段，添加日志警告
        if (columns.isEmpty()) {
            log.warn("未解析到任何字段: {}", sql);
        }

        table.setColumns(columns);
        return table;
    }

    private static void parseColumnComment(String sql, Map<String, String> comments) {
        Pattern pattern = Pattern.compile("(?i)COMMENT ON COLUMN\\s+(?:\"[^\"]+\"\\.)?\"([^\"]+)\"\\.\"([^\"]+)\"\\s+IS\\s+'([^']+)'");
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            String tableName = matcher.group(1);
            String columnName = matcher.group(2);
            String comment = matcher.group(3);
            comments.put(tableName.toLowerCase() + "." + columnName.toLowerCase(), comment);
        }
    }

    private static void parseTableComment(String sql, Map<String, String> comments) {
        Pattern pattern = Pattern.compile("(?i)COMMENT ON TABLE\\s+(?:\"[^\"]+\"\\.)?\"([^\"]+)\"\\s+IS\\s+'([^']+)'");
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            String tableName = matcher.group(1);
            String comment = matcher.group(2);
            comments.put("table." + tableName.toLowerCase(), comment);
        }
    }

    // 字段类型映射处理
    private static String getFieldType(String typeStr) {
        if (typeStr == null || typeStr.trim().isEmpty()) {
            return "UNKNOWN";
        }

        typeStr = typeStr.toUpperCase();

        // 常见类型映射
        Map<String, String> typeMapping = new HashMap<>();
        typeMapping.put("INT", "INTEGER");
        typeMapping.put("INT4", "INTEGER");
        typeMapping.put("INT8", "BIGINT");
        typeMapping.put("VARCHAR", "TEXT");
        typeMapping.put("CHAR", "TEXT");
        typeMapping.put("BOOL", "BOOLEAN");
        typeMapping.put("NUMERIC", "DECIMAL");
        typeMapping.put("SERIAL", "INTEGER");
        typeMapping.put("BIGSERIAL", "BIGINT");
        typeMapping.put("FLOAT4", "REAL");
        typeMapping.put("FLOAT8", "DOUBLE PRECISION");
        typeMapping.put("TIMESTAMPTZ", "TIMESTAMP WITH TIME ZONE");
        typeMapping.put("TEXT", "TEXT");

        // 返回映射后的类型或原始类型
        return typeMapping.getOrDefault(typeStr, typeStr);
    }

    @Data
    public static class SqlTable {
        private String tableName;
        private String description;
        private List<SqlColumn> columns;
    }

    @Data
    public static class SqlColumn {
        private String name;
        private String type;
        private String comment;
    }
}